#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project    :icac2 
@File       :get_data.py
@Author     :wangfan13
@Date       :2021/5/28 14:43 
"""
import os
from time import strftime, localtime

import py7zr
import shutil
import logging
import pymssql
import pandas as pd
from django.db import connection
from django.http import JsonResponse
from django.conf import settings

logger = logging.getLogger(__file__)

share_path = settings.SHARE_PATH
extra_path = settings.EXTRA_PATH


def file_upzip(path, e_path, settlement_no):
    # 解压路径
    extract_path = os.path.join(e_path, settlement_no)
    # 解压zip文件
    if path.endswith('.7z'):
        with py7zr.SevenZipFile(path, mode='r') as zip_file:
            zip_file.extractall(extract_path)


# path 需要查找的文件路径
# name 文件名称
def file_search(path, name):
    file_path_list = []
    for root, dirs, files in os.walk(path):  # path 为根目录
        if name in files:
            root = str(root)
            file_path_list.append(os.path.join(root, name))
    return file_path_list


# 检查压缩包内是否有目标文件
def package_check(path, dest_list):
    flag = False
    fp = open(path, 'rb')
    archive = py7zr.SevenZipFile(fp).getnames()
    for file_name in archive:
        for name in dest_list:
            if name in file_name:
                flag = True
                return flag
    return flag


# 数据篡改，关键字统一
def key_word_format(columns):
    order_time_name = '下单时间'
    if '下单时间' in columns:
        order_time_name = '下单时间'
    if '订单时间' in columns:
        order_time_name = '订单时间'
    if '时间点' in columns:
        time_point = '时间点'
    if '时间点方案1' in columns:
        time_point = '时间点方案1'
    else:
        time_point = ''
    return order_time_name, time_point


#  模糊查询文件路径
def file_fuzzy_search(path, name):
    file_path_list = []
    for root, dirs, files in os.walk(path):  # path 为根目录
        for file in files:
            if name in file:
                root = str(root)
                file_path_list.append(os.path.join(root, file))
    return file_path_list


def pymssql_conn():
    conn = pymssql.connect(
        host=settings.MSSQL_HOST,
        user=settings.MSSQL_USER,
        password=settings.MSSQL_PASSWORD,
        database=settings.MSSQL_DATABASE)
    return conn

# 定时执行3小时  生成订单重复表数据
def rb_check():
    share_path = settings.SHARE_PATH
    extra_path = settings.EXTRA_PATH
    logger.info("订单重复执行表函数开始时间", strftime("%Y-%m-%d %H:%M:%S", localtime()))
    print("订单重复执行表函数开始时间", strftime("%Y-%m-%d %H:%M:%S", localtime()))
    conn = pymssql_conn()
    cur = conn.cursor()
    sql = "select DISTINCT SettleCode,PolicyOwnerITCode,CONVERT(nvarchar(2000), OtherURL) OtherURL," \
          "SubmitDate,CONVERT(nvarchar(2000), Name),CONVERT(nvarchar(2000), DepartmentName)" \
          " from RBSettlementApply left outer JOIN ProjectPackage" \
          " ON dbo.RBSettlementApply.PackageID = ProjectPackage.PackageID " \
          " where Type != 0 and " \
          "OtherURL is not null and OtherURL like '%.7z' and OtherURL like '%结算%' and OtherURL not like " \
          "'%内%' and OtherURL not like '%货%'  and OtherURL not like '%台%' and OtherURL not like '%文件%'  " \
          "and OtherURL not like '%公%'  and OtherURL not like '%东%' and OtherURL not like '%商%' and " \
          "OtherURL not like '%售%' and OtherURL not like '%项%' and OtherURL not like '%江%' and OtherURL " \
          "not like '%Q1%' and OtherURL not like '%Q2%' and OtherURL not like '%Q3%' and OtherURL not " \
          "like '%Q4%' and OtherURL not like '%戏%' and OtherURL not like '%销%' and OtherURL not like " \
          "'%财%' and OtherURL not like '%市%' and OtherURL not like '%案%' and OtherURL not like '%件%' " \
          "and OtherURL not like '%过%' and OtherURL not like '%数%' and OtherURL not like '%料%' and OtherURL" \
          " not like '%ISV%' and OtherURL not like '%月%' and OtherURL not like '%年%' and  OtherURL not like" \
          " '%爱%' and OtherURL not like '%库存%' and OtherURL not like '%上%' and OtherURL not like '%批%' " \
          "and OtherURL not like '%暑%' and OtherURL not like '%盘%' and OtherURL not like '%加%' and OtherURL" \
          " not like '%店%' and OtherURL not like '%广%' and OtherURL not like '%高%' and OtherURL not like " \
          "'%略%' and OtherURL not like '%激%' and OtherURL not like '%活%' and OtherURL not like '%明%' and " \
          "OtherURL not like '%JD%' and OtherURL not like '%寒%' and OtherURL not like '%企%' and OtherURL not " \
          "like '%晋%' and OtherURL not like '%蒙%' and OtherURL not like '%区%' and OtherURL not like '%阳%' " \
          "and OtherURL not like '%麦%' and OtherURL not like '%歌%' and OtherURL not like '%DT%' collate " \
          "Chinese_PRC_CS_AI and IsCN!=1 order by SubmitDate;"
    cur.execute(sql)
    print(share_path, extra_path)
    print("查询完成时间", strftime("%Y-%m-%d %H:%M:%S", localtime()))
    settlement_package_dict = {}
    settlement_owner_dict = {}
    settlement_SubmitDate_dict = {}
    settlement_name_dict = {}
    settlement_dept_dict = {}
    ft = cur.fetchall()
    print("满足sql条件的结算案有:", len(ft), '件')
    for item in ft:
        settlement_no = item[0][:-3]
        if settlement_package_dict.get(settlement_no, -1) != -1:
            settlement_package_dict[settlement_no].append(item[2])
        elif settlement_package_dict.get(settlement_no, -1) == -1:
            settlement_package_dict[settlement_no] = [item[2]]
            settlement_owner_dict[settlement_no] = item[1]
            settlement_SubmitDate_dict[settlement_no] = item[3]
            settlement_name_dict[settlement_no] = item[4]
            settlement_dept_dict[settlement_no] = item[5]
    settle_no_list = list(settlement_package_dict.keys())
    for key in settle_no_list:
        if len(settlement_package_dict[key]) <= 1:
            settlement_package_dict.pop(key)
            settlement_owner_dict.pop(key)
            settlement_SubmitDate_dict.pop(key)
            settlement_name_dict.pop(key)
            settlement_dept_dict.pop(key)
    settle_no_list = list(settlement_package_dict.keys())
    print('目标结算案共有:', len(settle_no_list), '件')
    # 如果是插入、删除、更新语句切记要写提交命令con.commit()
    cur.close()
    conn.close()
    # 解压文件
    for no in settle_no_list:
        # 搜索路径
        for package_name in settlement_package_dict[no]:
            file_dir = os.path.join(share_path, package_name)
            file_upzip(file_dir, extra_path, no)
            print("当前正在解压：", package_name)
    print("解压完成时间", strftime("%Y-%m-%d %H:%M:%S", localtime()))
    # 筛选满足条件的结算案
    for no in settle_no_list:
        # 解压文件所在路径
        settlement_dir = os.path.join(extra_path, no)
        count = 0
        for file in os.listdir(settlement_dir):
            if '.' not in file:
                count = count + 1
        if count <= 1:
            settlement_package_dict.pop(no)
            if os.path.isdir(settlement_dir):
                # 删除目录及以内的所有文件
                shutil.rmtree(settlement_dir, True)
    settlement_no_list = list(settlement_package_dict.keys())
    print("筛选完成时间：", strftime("%Y-%m-%d %H:%M:%S", localtime()))
    conn = connection
    cursor = conn.cursor()
    truncate_sql = "truncate table RB_Settle_Check;"
    cursor.execute(truncate_sql)
    conn.commit()
    for no in settlement_no_list:
        # 解压文件所在路径
        settlement_df = pd.DataFrame()
        res_df = pd.DataFrame()
        file_path = os.path.join(extra_path, no)
        name = '订单明细结算名单明细.xlsx'
        file_list = file_search(file_path, name)
        res_tuple_list = []
        for fp in file_list:
            # 返回的是Excel中所有数据，数据格式为字典，{'0','content','1':'content'} ,0为shell1,1为shell2
            order_df_dict = pd.read_excel(fp, None)
            sheet_list = list(order_df_dict.keys())
            for sheet in sheet_list:
                if '补偿单价' in order_df_dict[sheet].columns and '补偿总价' in order_df_dict[sheet].columns:
                    order_df = order_df_dict[sheet]
                    """
                    当想要删除缺失值大于几个的某行，而缺失值小于几个的那些行不删除，就可以用到 df.dropna(thresh=a)设置，其中，a是设定的几
                    丢弃‘补偿单价’和‘补偿总价’缺失的列
                    axis：default 0指行,1为列
                    how：{‘any’, ‘all’}, default ‘any’指带缺失值的所有行;'all’指清除全是缺失值的
                    thresh：int,保留含有int个非空值的行
                    subset：对特定的列进行缺失值删除处理
                    inplace：这个很常见,True表示直接在原数据上更改
                    """
                    order_df.dropna(subset=['补偿单价', '补偿总价'], axis=0, inplace=True)
                    order_df = order_df[order_df['补偿单价'] > 0]
                    compare_order_df = order_df[['订单号', '代理商全称', '补偿总价']]
                    settlement_df = settlement_df.append(compare_order_df)

            res_df = settlement_df.groupby(by=['订单号', '代理商全称']).agg({'订单号': 'count', '补偿总价': {'sum', 'mean'}, })
            # 将会将原来的索引index作为新的一列，
            res_df = res_df.reset_index()
            res_df.columns = ['订单号', '代理商', '重复次数', '应返金额', '实际金额']
            res_df = res_df[res_df['重复次数'] >= 2]
            res_tuple_list = []
            for row in res_df.itertuples():
                row_list = [no, getattr(row, '订单号'), getattr(row, '代理商'), settlement_name_dict[no],
                            str(settlement_SubmitDate_dict[no]),
                            settlement_owner_dict[no].lower(), settlement_dept_dict[no],
                            getattr(row, '重复次数'), getattr(row, '应返金额'), round(getattr(row, '实际金额'), 2),
                            strftime("%Y-%m-%d %H:%M:%S", localtime())
                            ]
                res_tuple_list.append(tuple(row_list))
        insert_sql = "insert into RB_Settle_Check (settlecode,ordercode,agent,settlement_name,submitdate," \
                     "settleowner,settlement_dept,reaptetimes," \
                     "shouldrefund,realrefund,updatetime) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        conn.ping()
        cursor.executemany(insert_sql, res_tuple_list)
        conn.commit()
    print("结束时间", strftime("%Y-%m-%d %H:%M:%S", localtime()))
    conn.close()


# 定时执行 15分钟  生成人员名单监视表数据
def update_name_list():
    logger.info("人员名单监视函数开始执行", strftime("%Y-%m-%d %H:%M:%S", localtime()))
    print("人员名单监视函数开始执行", strftime("%Y-%m-%d %H:%M:%S", localtime()))
    sql = r"SELECT distinct lower(PolicyOwnerITCode) as PolicyOwnerITCode,sum(totalamount) as person_total_amount" \
          r" FROM RBSettlementApply where OtherURL like '%.7z'and totalamount > 0 " \
          r"group by lower(PolicyOwnerITCode) order by person_total_amount desc"
    conn = pymssql_conn()
    cur = conn.cursor()
    cur.execute(sql)
    res = cur.fetchall()
    mysql_conn = connection
    cursor = mysql_conn.cursor()
    for row in res:
        sql = "update monitor_name_list set total_amount = %s where name = %s"
        cursor.execute(sql, (str(row[1]), '0', str(row[0])))
        effect_row = cursor.rowcount
        mysql_conn.commit()
        if effect_row == 0:
            sql = "insert into monitor_name_list (name,total_amount,is_monitor) values (%s,%s,%s)"
            cursor.execute(sql, (row[0], row[1], '0'))
            mysql_conn.commit()
    return JsonResponse(res, safe=False)


# 展示监控名单
def show_monitor_list(request):
    page_size = request.params['page_size']
    page_number = request.params['page_number']
    offset = page_size * page_number
    mysql_conn = connection
    cursor = mysql_conn.cursor()
    sql = "select * from monitor_name_list order by total_amount desc limit %d offset %d" % (page_size, offset)
    cursor.execute(sql)
    res = cursor.fetchall()
    return JsonResponse(res, safe=False)


# 修改监控名单
def modify_monitor_lsit(request):
    add_moitor_list = request.params['add_list']
    conn = connection
    cursor = conn.cursor()
    sql = "update monitor_name_list set is_monitor=1 where name=%s"
    cursor.executemany(sql, add_moitor_list)
    del_monitor_list = request.params['remove_list']
    for name in del_monitor_list:
        sql = "select distinct case_owner_itcode from case_case where case_owner_itcode =%s" % name
        cursor.execute(sql)
        if len(cursor.fetchall()) >= 0:
            return JsonResponse("删除失败，待删除的人中有人有案件！", safe=False)
    sql = "update monitor_name_list set is_monitor=0 where name=%s"
    cursor.executemany(sql, add_moitor_list)
    return JsonResponse("修改成功！", safe=False)


# 数据篡改检查
def data_cheat_check(request):
    print(extra_path + "aaaaaaa")
    logger.info("数据篡改检查函数开始执行时间", strftime("%Y-%m-%d %H:%M:%S", localtime()))
    print("数据篡改检查函数开始执行时间_打印", strftime("%Y-%m-%d %H:%M:%S", localtime()))
    global process_percentage

    conn = connection
    cursor = conn.cursor()
    name_list_sql = "select name from monitor_name_list where is_monitor=1;"
    cursor.execute(name_list_sql)
    list_res = cursor.fetchall()
    total_size = len(list_res)
    ff_df = pd.DataFrame()
    for index, name in enumerate(list_res):
        process_percentage = round(index / total_size, 4)
        print(str(process_percentage * 100) + '%', name[0])
        sql = "SELECT settlecode,budgetCode,TotalAmount,SubmitDate,PolicyOwnerITCode,CONVERT(nvarchar(2000), OtherURL) " \
              "OtherURL,CONVERT(nvarchar(2000), DepartmentName) DepartmentName FROM RBSettlementApply " \
              " left outer JOIN ProjectPackage   ON dbo.RBSettlementApply.PackageID = ProjectPackage.PackageID " \
              "where PolicyOwnerITCode like '%%%s%%' and OtherURL " \
              "like '%%.7z' and totalamount>0 and OtherURL not like '%%返%%' and OtherURL not like '%%批%%'" % (name[0])
        conn = pymssql_conn()
        cur = conn.cursor()
        cur.execute(sql)
        ft = cur.fetchall()
        url_dict = {}
        budget_code_dict = {}
        total_amount_dict = {}
        submit_date_dict = {}
        policy_owner_dict = {}
        settlement_dept_dict = {}
        his_package = {}
        for item in ft:
            url_dict[item[0]] = item[5]
            budget_code_dict[item[0]] = item[1]
            total_amount_dict[item[0]] = item[2]
            submit_date_dict[item[0]] = item[3]
            # policy_owner_dict[item[0]] = item[4]
            # 将策划人itcode变成小写
            policy_owner_dict[item[0]] = item[4].lower()
            settlement_dept_dict[item[0]] = item[5]
        settle_list = list(url_dict.keys())
        for no in settle_list:
            package_name = url_dict[no]
            if package_name not in his_package.keys():
                his_package[package_name] = 1
                dest_list = ['计算']
                file_dir = os.path.join(share_path, package_name)
                # 检查压缩包内是否有目标文件
                if package_check(file_dir, dest_list):
                    file_upzip(file_dir, extra_path, no)
                    print("当前正在解压：", package_name)

            else:
                url_dict.pop(no)

        # 删掉非目标结算案
        settle_list = list(url_dict.keys())
        for no in settle_list:
            file_path = os.path.join(extra_path, no)
            if len(file_fuzzy_search(file_path, '计算')) == 0:
                url_dict.pop(no)
                if os.path.isdir(file_path):
                    shutil.rmtree(file_path, True)
        settle_list = list(url_dict.keys())
        final_df = pd.DataFrame()
        for no in settle_list:
            file_path = os.path.join(extra_path, no)
            name = '计算'
            file_list = file_fuzzy_search(file_path, name)
            for path in file_list:
                RB_BS_df = pd.read_excel(path, None)
                sheet_list = list(RB_BS_df.keys())
                for sheet in sheet_list:
                    if set(['订单SO', '订单PO', 'T1代理名称', '订单产品总价', '产品单价', '产品数量']) <= set(RB_BS_df[sheet].columns):
                        order_time_name, time_point = key_word_format(RB_BS_df[sheet].columns)
                        dest_df = RB_BS_df[sheet][
                            ['订单SO', '订单PO', time_point, order_time_name, 'T1代理名称', '订单产品总价', '产品单价', '产品数量']]
                        dest_df.columns = ['订单SO', '订单PO', '时间点', '下单时间', 'T1代理名称', '订单产品总价', '产品单价', '产品数量']
                        dest_df = dest_df.copy()
                        dest_df['结算案编号'] = no
                        dest_df['政策解释人'] = policy_owner_dict[no]
                        dest_df['策划案提交时间'] = submit_date_dict[no]
                        # dest_df['策划人所属部门'] = settlement_dept_dict[no]
                        dest_df['政策解释人所属部门'] = settlement_dept_dict[no]
                        dest_df = dest_df.copy()
                        """
                         向dataframe对象中添加新的行，如果添加的列名不在dataframe对象中，将会被当作新的列进行添加
                         other：另一个df；
                         ignore_index：若为True，则对index进行重排；
                         verify_integrity：对index的唯一性进行验证，若有重复，报错。若已经设置了ignore_index，则该参数无效。
                        """
                        final_df = final_df.append(dest_df, ignore_index=True)
                        # 新家一个存疑类型字段
                        final_df['存疑类型'] = final_df.apply(
                            lambda x: "订单总价计算有误" if float(x[5]) != float(x[6]) * float(x[7]) else '', axis=1)
        ff_df = ff_df.append(final_df)

    if ff_df.empty is False:
        from sqlalchemy import create_engine
        engine = create_engine("mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8" % (
            settings.DATABASES.get("default").get("USER", 'root'),
            settings.DATABASES.get("default").get("PASSWORD", "root"),
            settings.DATABASES.get("default").get("HOST", "172.17.240.153"),
            int(settings.DATABASES.get("default").get("PORT", 3306)),
            settings.DATABASES.get("default").get("NAME", "icac")
        ))
        ff_df.to_sql("datacheat", engine, if_exists='replace', index=False)
        time_check()
    print("完成时间", strftime("%Y-%m-%d %H:%M:%S", localtime()))
    return JsonResponse("数据篡改检查完成！", safe=False)


def time_check():
    conn = connection
    cursor = conn.cursor()
    sql = "truncate table datacheat_1"
    cursor.execute(sql)
    sql = "insert into datacheat_1" \
          "(select 订单SO,订单PO,时间点,下单时间,T1代理名称,订单产品总价,产品单价," \
          "产品数量,结算案编号,政策解释人,策划案提交时间,策划人所属部门,if(bo is null or bo='',存疑类型,concat(\'时间点有误;\',存疑类型)) as 存疑类型 " \
          "from(select a.*,b.ordernum as bo from datacheat a left join " \
          "(select * from tp_cheat_record where wrong = 1) b on a.订单SO = b.ordernum) tmp)"
    cursor.execute(sql)
    conn.commit()
    return JsonResponse("时间点检查完成！", safe=False)


def process_show():
    return JsonResponse(process_percentage, safe=False)
